﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections.Specialized;
using System.Data;

namespace SGRapidForm.Common
{
    public class SqlHelper
    {
        /// <summary>
        /// 检测SQL参数并且构造SQL语句
        /// </summary>
        /// <example>
        /// string sql = GenerateSqlStatement(fieldColletion, 'username', DbType.String, " AND Username like '%{0}%' ")
        /// </example>
        /// <remarks>
        /// 检测完成三件事：1.参数是否存在;2.参数类型是否正确;3.参数值过滤;
        /// </remarks>
        /// <param name="fieldColletion">参数键值对集合</param>
        /// <param name="fieldNam">字段名称</param>
        /// <param name="fieldType">字段类型</param>
        /// <param name="sql">构造语句格式</param>
        /// <returns>Sql语句</returns>
        public static string GenerateSqlStatement(NameValueCollection fieldColletion, string fieldName, DbType fieldType, string sql)
        {
            if (fieldColletion.AllKeys.Contains(fieldName) && DetectionAndFiltering.DetectParameterType(fieldColletion[fieldName], fieldType))
            {
                return String.Format(sql, DetectionAndFiltering.FilterAParameter(fieldColletion[fieldName]));
            }
            return string.Empty;
        }

        /// <summary>
        ///  过滤一个SQL参数值
        /// </summary>
        /// <param name="fieldValue">字段值</param>
        /// <param name="fieldType">字段类型</param>
        /// <returns>SQL参数值</returns>
        public static string GenerateSqlParameter(string fieldValue, DbType fieldType)
        {
            if (DetectionAndFiltering.DetectParameterType(fieldValue, fieldType))
            {
                return DetectionAndFiltering.FilterAParameter(fieldValue);
            }
            return string.Empty;
        }

        /// <summary>
        /// 过滤查询参数，并按照制定格式返回SQL语句
        /// </summary>
        /// <param name="parameterValue">参数值</param>
        /// <returns></returns>
        public static string FilterParameter(string parameterValue, DbType fieldType)
        {
            if (DetectionAndFiltering.DetectParameterType(parameterValue, fieldType))
            {
                return DetectionAndFiltering.FilterAParameter(parameterValue);
            }
            return string.Empty;
        }

        /// <summary>
        /// 过滤字符型查询参数
        /// </summary>
        /// <param name="parameterValue"></param>
        /// <returns></returns>
        public static string FilterStringParameter(string parameterValue)
        {
            if (parameterValue == null) 
            { 
                return string.Empty; 
            }
            return DetectionAndFiltering.FilterAParameter(parameterValue);
        }

        /// <summary>
        /// 把keyids集合转成Sql语句，其中对每个参数进行过滤
        /// </summary>
        /// <example>
        /// "03B63B26-E808-4EA7-91D3-2809243FAE69_03D87DFD-CBFD-43A3-890A-F3F5F4B8DD86"
        ///  转成
        /// ‘03B63B26-E808-4EA7-91D3-2809243FAE69’，‘03D87DFD-CBFD-43A3-890A-F3F5F4B8DD86’
        /// </example>
        /// <param name="keyids">keyids集合</param>
        /// <returns>Sql语句</returns>
        public static string ConvertKeyidsToSqlStatement(string keyids)
        {
            if (string.IsNullOrEmpty(keyids))
            {
                throw new ArgumentException("keyids 参数不能为空，无法转换");
            }
            string[] keyidsArray = keyids.Split('_');
            int count = keyidsArray.Length;
            string[] result = new string[count];
            for (int i = 0; i < count; i++)
            {
                if (string.IsNullOrEmpty(keyidsArray[i]))
                {
                    throw new ArgumentException("keyid 参数不能为空，无法转换");
                }
                result[i] = string.Format("'{0}'", SqlHelper.FilterStringParameter(keyidsArray[i]));
            }
            return string.Join(",", result);
        }

        /// <summary>
        /// 统计Keyids个数
        /// </summary>
        /// <param name="keyids"></param>
        /// <returns></returns>
        public static int GetKeyidsCount(string keyids)
        {
            return StrHelper.SubString(keyids, "_") + 1;
        }

        /// <summary>
        /// 把查询字符串转成成NameValueCollection
        /// </summary>
        /// <param name="query">查询字符串</param>
        /// <returns></returns>
        public static NameValueCollection ParseQueryString(string query)
        {
            return HttpUtility.ParseQueryString(query);
        }

        /// <summary>
        /// 处理列设置语句
        /// 主要是验证不能为空，并且删除最后一个字符","
        /// </summary>
        /// <param name="columnSettings">列设置Sql</param>
        /// <returns></returns>
        public static string ProcessColumnSettings(string columnSettings)
        {
            if (string.IsNullOrEmpty(columnSettings))
            {
                throw new ArgumentException("设置列参数错误");
            }
            int index = columnSettings.LastIndexOf(',');
            return columnSettings.Remove(index);
        }

   
    }
}